﻿use master
go

if exists (select * from sys.databases where name='va')
drop database va
go

create database va
go

use va
go

create function [dbo].[uftWordTokens]
  (
    @string ntext,
    @WordStartCharacters varchar(255) = 'a-z',
    @WordCharacters varchar(255) = '-a-z'''
  )
returns @Results table
  (
    SeqNo int identity(1, 1),
    Item varchar(255),
    TokenType int
  )
as /*
This table function produces a table which divides up the words and 
the spaces between the words in some text and produces a table of the
two types of token in the sequence in which they were found
*/
   begin
    declare @Pos int,    --index of current search
      @WhereWeAre int,--index into string so far
      @ii int,    --the number of words found so far
      @next int,  --where the next search starts 
      @size int   --the total size of the text

    select  @ii = 0, @WhereWeAre = 1, @size = DATALENGTH(@string)


    while @Size >= @WhereWeAre
      begin
        select  @pos = PATINDEX('%[' + @wordStartCharacters + ']%',
                                SUBSTRING(@string, @whereWeAre, 4000))
        if @pos > 0 
          begin
            if @pos > 1 
              insert  into @Results
                      ( item, tokentype )
                      select  SUBSTRING(@String, @whereWeAre, @pos - 1), 2
            select  @next = @WhereWeAre + @pos, @ii = @ii + 1
            select  @pos = PATINDEX('%[^' + @wordCharacters + ']%',
                                    SUBSTRING(@string, @next, 4000) + ' ')
            insert  into @Results
                    ( item, tokentype )
                    select  SUBSTRING(@String, @next - 1, @pos), 1
            select  @WhereWeAre = @next + @pos - 1
          end
        else 
          begin
            if LEN(REPLACE(
                    SUBSTRING(@String, @whereWeAre, 4000), ' ', '!'
            )) > 0 
              insert  into @Results
                      ( item, tokentype )
                      select  SUBSTRING(@String, @whereWeAre, 4000), 2
            select  @whereWeAre = @WhereWeAre + 4000
          end
      end
    return
   end 
go

--select  '[' + item + ']', tokentype
--from    dbo.uftWordTokens('This     has
-- been relentlessly 
--,^----tested', default, default)       
--select  '[' + item + ']', tokentype
--from    dbo.uftWordTokens('This has been relentlessly tested        !',
--                          default, default)        
--select  item, tokentype
--from    dbo.uftWordTokens('This has been', default, default)   
--select  '[' + item + ']', tokentype
--from    dbo.uftWordTokens(' <!-- 23 343.43  <div>Hello there  .... -->',
--                          default, default) 

create function dbo.ufiDifferencesInText
  (
    @Sample ntext,
    @comparison ntext
  )
returns int
as begin
    declare @results table
      (
        token_ID int identity(1, 1),
        sequenceNumber int,
        Sample_ID int,
        Item varchar(255),
        TokenType int
      )
/*
This function returns the number of differences it found between two pieces
of text
*/
    insert  into @results
            ( SequenceNumber, Sample_ID, Item, Tokentype )
            select  seqno, 1, item, tokentype
            from    dbo.uftWordTokens(@sample, default, default)

    insert  into @results
            ( SequenceNumber, Sample_ID, Item, Tokentype )
            select  seqno, 2, item, tokentype
            from    dbo.uftWordTokens(@comparison, default, default)
    declare @closestMatch table
      (
        sequenceNumber int,
        skew int
      )
    insert  into @closestMatch
            ( sequencenumber, skew )
            select  COALESCE(a.sequencenumber, b.sequencenumber),
                    COALESCE(MIN(ABS(COALESCE(b.sequenceNumber, 1000) 
                        - COALESCE(a.sequencenumber, 1000))),
                             -1)
            from    ( select  *
                      from    @results
                      where   sample_ID = 1 and tokentype = 1
                    ) as a FULL OUTER JOIN ( select  *
                                          from    @results
                                          where   sample_ID = 2 
                                              and tokentype = 1
                                        ) b on a.item = b.item
            group by COALESCE(a.sequencenumber, b.sequencenumber)
            order by COALESCE(a.sequencenumber, b.sequencenumber)



    return ( select SUM(case when a.skew - b.skew = 0 then 0
                             else 1
                        end)
             from   @closestmatch a inner join @closestMatch b 
                  on b.sequenceNumber = a.sequenceNumber + 2
           )
   end
go 


select  dbo.ufiDifferencesInText(
	'
This table function produces a table which divides up the words and 
the spaces between the words in some text and produces a table of the
two types of token in the sequence in which they were found'
,
	'
This table function produces a table which divides up the words and 
the spaces between the words in some text and produces a table of the
two types of token in the sequence in which they were found')
go


create function dbo.[UDF_Word_Count_From_String]
(
 @vsText  nvarchar(MAX) -- Variable for string
)
returns  int
begin
    declare @viRet int
 
    set @vsText = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vsText
    ,char(9),char(32))
    ,char(13),char(32))
    ,char(10),char(32))
    ,char(32),'øù')
    ,'ùø','')
    ,'øù',char(32))
    select @viRET =LEN(@vsText) - LEN(REPLACE(@vsText,' ', '')) + 1
return @viRET
end
go


create function dbo.[String_Is_Similar]
(
	@vsSource nvarchar(MAX),
	@vsDestinate nvarchar(MAX)
)
returns bit
begin

	declare @viDiffs int
	set @viDiffs = dbo.ufiDifferencesInText(@vsSource, @vsDestinate)
	if @viDiffs = 0
		return 1

	declare @viWordCount int
	set @viWordCount = dbo.UDF_Word_Count_From_String(@vsSource)

	

	if (@viWordCount / @viDiffs) > 5
		return 1

	return 0
end
go
